SQL Serverのデータベース実体をファイル,Diskの中身から探る
引用元
https://gyazo.com/53671da65807b29e83d83d54d3176b56
データベースの実体であるデータファイルとトランザクションログファイルについてざっくり説明する. 第二章Disk I/O操作の説明が該当箇所である. 加えて関連が強い第四章データベース構造の原理も追記している.
関連: SQL Serverのテーブル,オブジェクトの実現方法としてのデータページ構成~行ストアと列ストア~
公式ドキュメント: ページとエクステントのアーキテクチャ ガイド https://docs.microsoft.com/ja-jp/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15
公式: SQL Server トランザクション ログのアーキテクチャと管理ガイド https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15
--.icon
データベースの実体とは??
データベースといえば, Client applicationが出力としてExcelのような表出力をしてくれるので, 実際のデータ実体を意識しなくてよい. データ, データベースは実体(物理)はファイルである(textファイルのようなもの).
構成内容は2つ
Data file( .mdf ) ... データ本体が格納されている. 実際にCRUDされる際には当物理ファイルが処理対象となり, データベースエンジンやClientが「いい感じ」に加工処理してくれる. データファイルは物理的には 論理的には8KB毎のブロックに分割して管理されており,8KBを1単位としてページと呼称されて管理されている. 物理的にはもう少し大きな容量のファイルである.
Transaction Log file( .ldf ) ... データへの更新, 変更履歴が管理されている. 変更前にTransaction Logは作成されて, 当Log Fileを元に実際のデータ更新はなされる.
実際の処理イメージ
https://gyazo.com/5c5c1eec2597207498af6e1027723263
抑えておくべき, SQL ServerのI/O処理の動作
https://gyazo.com/d3889307617f2435607471d5b090d0fe
Buffer Cache ... Diskから毎回データを読み書きすると, その回数分だけDisk⇔Memoryへのデータ展開が必要となる. 予期されるデータ読み書きについてはMemory(Buffer Cache)上に保持しておくことで処理のOverheadを減らすことが可能である.
上記画像では,Buffer CacheとDiskの差分解消方法としてCheckpointを紹介しているが, 他には集中書き込み(Eager Write)やLazy Writeの書き込み手法が存在する.
SQL ServerとWindowsOSのファイルのやり取り
データベースの実体はファイルであることは上述の通り. 該当ファイルの処理, 操作のためにWindowsOSが提供しているWin32 APIという関数を利用する.
Createfile関数, ReadFileScatter関数, WriteFileGather関数が代表的な関数であり, それぞれファイルの作成, 読み込み, 書き込みが機能として提供されている.
I/O, Disk領域におけるモニタリング方法
利用ツールとして, 動的ビュー( sys.dm_os_wait_stats )を参照することが可能
実行コマンド
code:sql
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
データベース実現方法をデータファイルの論理構成から理解する
データベースの実体はDatafileとTransactionLogfileであると説明した. Datafileの中身, 詳細からデータベース(論理的なテーブル構造など)をどのように実現しているのかを捉える.
オブジェクト/テーブルとデータファイルの関係
https://gyazo.com/4d909b7f747b8afdae3b19973cad301d
データファイルを論理的に8KB単位で区切り1ページとして処理の最小単位として管理する. 更に8ページごとのまとまり/グループをエクステントとして管理する. オブジェクト/テーブルへの割当単位はエクステント単位とする.
エクステントには, 単一テーブル/オブジェクトの行が管理されている単一エクステントと, 複数オブジェクトデータが管理される混合エクステントに分けられる. 2016年以降は単一エクステントが主流である. 混合エクステントの場合, Diskの効率利用が可能だが, データ探索のOverheadが大きいためだ.
全てのページは8KBの最初から96バイトの部分までをページヘッダーという管理情報を格納するための領域として使用. ページが所属するテーブルに割り当てられたオブジェクトIDなど.
ページの中身
データファイルのページ内にはいくつかの種類のデータが格納されている.
データそのもの ... データまたはインデックス, ラージオブジェクト(text, image, xml , etc ...), 8KBを超過した可変長カラムデータ
GAM(Global Allocatoin Map) ... エクステントの割当状況
SGAM(Share Global Allocatoin Map) ... エクセステントが混合エクステントであるかを管理
PFS(Page Free Space) ... 8KBページ毎の使用状況(使用済み, 未使用)や使用量(~~%の容量)
IAM(Index Allocation map) ... クラスタ化インデックス, 非クラスタ化インデックス及びヒープのオブジェクトが利用しているエクステントの結びつきを管理している.
#Azure #Database #SQLServer